IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_PageGetFrontEndPage]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_PageGetFrontEndPage]
GO
create procedure [dbo].[cms_sp_PageGetFrontEndPage]
(
@SiteGroupId int,
@SiteId int,
@SystemLanguageId int,
@PageUrl [nvarchar](250),
@PageTypeParameter nvarchar(400),
@PageControlTypeParameter nvarchar(150)
)
as
declare @PageId int
declare @TemplateId int
declare @IsPublished bit
declare @PublishStartDate datetime
declare @PublishEndDate datetime
set @IsPublished = 1

select 
	@PageId = Pages.PageId,
	@TemplateId = Pages.TemplateId,
	@PublishStartDate = EntityRevisions.PublishStartDate,
	@PublishEndDate = EntityRevisions.PublishEndDate
from [dbo].[Pages]
inner join [dbo].[EntityRevisions]
	on EntityRevisions.EntityId = Pages.PageId and EntityRevisions.EntityTypeParameter = @PageTypeParameter
where (([SiteGroupId] is null and [SiteId] is null) or 
	([SiteGroupId] = @SiteGroupId and [SiteId] is null) or
	([SiteGroupId] = @SiteGroupId and [SiteId] = @SiteId)) and 
	EntityRevisions.IsLatestCompletedRevision = 1 and
	EntityRevisions.IsPublished = @IsPublished and
	EntityRevisions.IsDeleted = 0 and
	Pages.PageUrl = @PageUrl

select 
	Pages.PageId,
	Pages.PublicPageId,
	(select 
		ChildPages.PageId
	from dbo.Pages as ChildPages
	inner join dbo.EntityRevisions
		on EntityRevisions.EntityId = ChildPages.PageId and 
		EntityRevisions.EntityTypeParameter = @PageTypeParameter and EntityRevisions.IsLatestRevision = 1
	where ChildPages.PageNodeId = Pages.PageNodeId.GetAncestor(1) and
		((ChildPages.[SiteGroupId] is null and ChildPages.[SiteId] is null) or 
		(ChildPages.[SiteGroupId] = @SiteGroupId and ChildPages.[SiteId] is null) or
		(ChildPages.[SiteGroupId] = @SiteGroupId and ChildPages.[SiteId] = @SiteId))) as ParentPageId,
	Pages.SiteGroupId,
	Pages.SiteId,
	Pages.SystemLanguageId,	
	Pages.PageTypeKey,
	Pages.PageBehaviourTypeKey,
	Pages.PageNodeId.GetLevel() as PageLevel,
	Pages.PageName,
	Pages.PageUrl,
	Pages.IsVisible,
	Pages.PageTitle,
	Pages.PageTitleOverride,
	Pages.PageDescription,
	Pages.PageKeywords,
	Pages.CreatedBy,
	Pages.DateCreated,
	Pages.LastUpdatedBy,
	Pages.DateLastUpdated,
	Pages.PageKey,
	@IsPublished as IsPublished,
	@PublishStartDate as PublishStartDate,
	@PublishEndDate as PublishEndDate,
	Templates.TemplateId,
	Templates.TemplateName,
	Templates.TemplateTypeKey,
	Templates.TemplateUrl
from dbo.Pages
inner join dbo.Templates
	on Templates.TemplateId = Pages.TemplateId
where Pages.PageId = @PageId and
	((Pages.SiteGroupId is null and Pages.SiteId is null) or 
	(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId is null) or
	(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId = @SiteId))

select
	@PageId as PageId,
	TemplateRegions.TemplateRegionId,
	TemplateRegions.TemplateRegionName,
	@TemplateId as TemplateId,
	InnerPageControls.PageControlId,
	InnerPageControls.ModuleId,
	InnerPageControls.PageControlTypeKey,
	InnerPageControls.PageControlViewValue,
	InnerPageControls.PageControlModelValue,
	InnerPageControls.PageControlContentId,
	InnerPageControls.PageControlOrder,
	InnerPageControls.PageControlContent
from dbo.TemplateRegions
left join 
	(select		
		PageControls.PageControlId,
		PageControls.PageId,
		PageControls.TemplateRegionId,
		PageControls.ModuleId,
		PageControls.PageControlTypeKey,
		PageControls.PageControlViewValue,
		PageControls.PageControlModelValue,
		PageControls.PageControlContentId,
		PageControls.PageControlOrder,
		(select			
			Contents.ContentBody 
		from dbo.Contents
		where Contents.ContentId = PageControls.PageControlContentId
		) as PageControlContent
	from dbo.PageControls
	inner join dbo.EntityRevisions
		on EntityRevisions.EntityId = PageControls.PageControlId and 
		EntityRevisions.EntityTypeParameter = @PageControlTypeParameter
	where EntityRevisions.IsLatestCompletedRevision = 1 and
		EntityRevisions.IsPublished = @IsPublished and
		EntityRevisions.IsDeleted = 0) as InnerPageControls
	on InnerPageControls.PageId = @PageId and InnerPageControls.TemplateRegionId = TemplateRegions.TemplateRegionId
where TemplateRegions.TemplateId = @TemplateId 
order by InnerPageControls.PageControlOrder
GO